﻿Imports SCIEF.ActiveFramework.CommonExt


Public Class PivotExt

    Private _aQueryName As String
    Private _aMetaQuery As CommonExt.MetaQuery
    Private _mqCtrlCode As String


    Public Event ShowingCustomizationForm As DevExpress.XtraPivotGrid.CustomizationFormShowingEventHandler

    Public Sub New()

        ' This call is required by the designer.
        ' This call is required by the designer.
        InitializeComponent()

    End Sub

    Public Sub New(ByVal aQueryname As String)

        ' This call is required by the designer.
        InitializeComponent()

        Me.QueryName = aQueryname
        initDataSourceBarEdit()

    End Sub

    Public Property QueryName() As String
        Get
            Return _aQueryName
        End Get
        Set(ByVal value As String)
            _aQueryName = value
        End Set
    End Property

    Private Sub loadPivot(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles OpenBarButton.ItemClick
        Dim aPivotFile As PivotFileDataSource
        Dim aFile As New System.Windows.Forms.OpenFileDialog
        aFile.FileName = "*.spt"
        If aFile.ShowDialog() = DialogResult.OK Then
            aPivotFile = New PivotFileDataSource(aFile.FileName)
            Me.PivotGridControl1.DataSource = aPivotFile
        End If
    End Sub

    Private Sub savePivot(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles SaveBarButton.ItemClick
        Dim aFile As New System.Windows.Forms.SaveFileDialog
        aFile.FileName = "*.spt"
        If aFile.ShowDialog = DialogResult.OK Then
            Me.PivotGridControl1.SavePivotGridToFile(aFile.FileName, True)
        End If
    End Sub


    Public Sub InitDataSourceBarEdit()
        Dim mainViewAccess As MetaQueryAccess = Nothing
        Dim mqCtrlCode As String = Nothing
        ' By Design in MDIExpress the pivotext instance is called with metaquery 'PIVOTSOURCE'
        _aMetaQuery = CommonExt.MetaQuery.GetMetaQueryByName(Me.QueryName)
        _aMetaQuery.FillResultQuery()
        '
        'Set Security on Pivot Queries
        For Each aRow As DataRow In _aMetaQuery.ResultQuery.Rows
            mqCtrlCode = AppManager.GetControlMqFromForm("FRM_00072", aRow("QueryName").ToString)
            If Not String.IsNullOrWhiteSpace(mqCtrlCode) Then
                mainViewAccess = New MetaQueryAccess(mqCtrlCode)
                'CShort(False) = 0
                If mainViewAccess.AllowRead = CShort(False) Then
                    aRow.Delete()
                End If
            Else
                aRow.Delete()
            End If
        Next
        '
        'outstandingissue : will have to check the language and get the 2nd or 3thr column of LOV according to LOV Standard 
        RepositoryItemLookUpEdit1.DataSource = _aMetaQuery.ResultQuery
        RepositoryItemLookUpEdit1.PopulateColumns()

        RepositoryItemLookUpEdit1.Columns("QueryID").Visible = False

        'RepositoryItemLookUpEdit1.Columns("DocumentTemplateId").Visible = False
        'RepositoryItemLookUpEdit1.Columns("TypeId_FK").Visible = False
        'RepositoryItemLookUpEdit1.Columns("Data").Visible = False

        RepositoryItemLookUpEdit1.NullText = "(none)"
        RepositoryItemLookUpEdit1.ValueMember = _aMetaQuery.ResultQuery.Columns("QueryId").ToString
        RepositoryItemLookUpEdit1.PopupFormSize = New Size(500, 300)
        RepositoryItemLookUpEdit1.DisplayMember = _aMetaQuery.ResultQuery.Columns("QueryName").ToString
        RepositoryItemLookUpEdit1.BestFitMode = DevExpress.XtraEditors.Controls.BestFitMode.BestFit
        RepositoryItemLookUpEdit1.SearchMode = DevExpress.XtraEditors.Controls.SearchMode.AutoComplete
        RepositoryItemLookUpEdit1.AutoSearchColumnIndex = 1
    End Sub

    Private Sub RepositoryItemLookUpEdit1_EditValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RepositoryItemLookUpEdit1.EditValueChanged

        'Late binding occurs...
        Me.Cursor = Cursors.WaitCursor
        Dim aLookupEdit As LookUpEdit = CType(sender, LookUpEdit)
        _aQueryName = CType(sender, LookUpEdit).Text
        _mqCtrlCode = aLookupEdit.GetColumnValue("ControlCD").ToString
        Me.PivotGridControl1.Fields.Clear()

        'OUTSTANDINGISSUE: Valider qu'il est nécessaire de setter ces 3 property pour montrer ou cacher les colonnes de totaux. clieng 2013-10-29.
        Me.PivotGridControl1.OptionsView.RowTotalsLocation = Nothing
        PivotGridControl1.OptionsView.ShowRowTotals = False
        PivotGridControl1.OptionsView.HideAllTotals()


        Me.Cursor = Cursors.Default
    End Sub

    Private Sub LoadPivotTableBySQLStatement(ByVal ametaqueryname As String)

        Dim sqlcmd As System.Data.SqlClient.SqlCommand = Nothing
        Dim aParamFrm As MqParamForm
        Dim aExecQuery As DialogResult = DialogResult.OK
        Dim mainViewAccess As MetaQueryAccess = Nothing

        Dim aUserID As Guid
        '
        PivotGridControl1.BeginUpdate()
        '
        Using aMq As MetaQuery = MetaQuery.GetMetaQueryByName(ametaqueryname)
            Dim reader As SqlClient.SqlDataReader = Nothing
            '
            '
            If aMq.CommandTypeID = Base.IdRepository.METALIST_METACOMMANDTYPE_SP AndAlso aMq.MetaParams.Rows.Count > 0 Then

                aParamFrm = New MqParamForm(aMq.QueryName)
                aExecQuery = aParamFrm.ShowDialog()
                'On dit OK pour la form de Parametre
                If aExecQuery = DialogResult.OK Then
                    'On va chercher les Parametre de la SP
                    'sqlcmd = MetaQuery.GetSPCmdByMeta(aMq, False)
                    sqlcmd = aMq.InitQueryParams
                    'On map les parametres de la SP avec les valeurs de la form aParamFrm.
                    'For Each param As SqlParameter In sqlcmd.Parameters
                    '    If param.Direction = ParameterDirection.Input Then
                    '        If Not IsDBNull(aParamFrm.SelectedParam(param.ParameterName)) Then
                    '            param.Value = aParamFrm.SelectedParam(param.ParameterName)
                    '        End If
                    '    End If
                    'Next

                    'On prend les parametre défini par la MQ, pour les retrouver dans le sqlCmd.Parameters
                    'Les metaColumns on la priorité
                    Dim param As SqlParameter

                    'on recherche si la security du AllowRead = 2 soit ( for logged in User) 2 est une valuer by design
                    'un parametre visible = 0 sur les metacolumn permets ainsi d'être discret sur le user.
                    'onprend  le Logged in user id ensuite ... le scoping est prit en charge par la Query.
                    ' a noter que le developper SQL a la responsabilité de faire le scoping par User ou la hierarchie du User

                    'mqCtrlCode = AppManager.GetControlMqFromForm("FRM_00072", aRow("QueryName").ToString)
                    If Not String.IsNullOrWhiteSpace(_mqCtrlCode) Then
                        mainViewAccess = New MetaQueryAccess(_mqCtrlCode)
                        'CShort(False) = 0
                        If mainViewAccess.AllowRead = 2 Then
                            aUserID = AppManager.UserID
                        End If
                    End If
                    For Each row As DataRow In aMq.MetaParams.Rows
                        If sqlcmd.Parameters.Contains(row("ColumnName").ToString) Then
                            param = sqlcmd.Parameters(row("ColumnName").ToString)
                            If param IsNot Nothing AndAlso Not IsDBNull(aParamFrm.SelectedParam(row("ColumnName").ToString)) Then
                                param.Value = aParamFrm.SelectedParam(row("ColumnName").ToString)
                            End If


                        Else
                            MessageWindow.ShowMessage(New Exception(String.Format("Pivot Execution, SQL Parameter not found: {0}", row("ColumnName"))))
                        End If
                    Next
                ElseIf aExecQuery = DialogResult.Cancel Then
                    Exit Sub
                End If
                'On Passe la sqcmd qui a les paramêtre de rentré
                aMq.FillResultQuery(sqlCmd:=sqlcmd)
            Else
                aMq.FillResultQuery()
            End If
            '
            ' Assign Result to Pivot Table
            Me.PivotGridControl1.DataSource = Nothing
            Me.PivotGridControl1.DataSource = aMq.ResultQuery.DefaultView
            Dim tableColumns As DataTable = CommonExt.Base.AppManager.GetUserColumnSecurity(aMq.ResultQuery.TableName, CommonExt.Base.AppManager.UserID)
            Dim aPivotFields As PivotGridFieldCollection
            Dim formatType As DevExpress.Utils.FormatType
            If Me.PivotGridControl1.Fields.Count = 0 Then
                aPivotFields = Me.PivotGridControl1.Fields
                aPivotFields.Clear()
                Dim aField As PivotGridField
                For Each _row As DataRow In tableColumns.Rows
                    If CInt(_row("pVisible")) <> 3 Then
                        aField = aPivotFields.Add(_row("ColumnName").ToString, PivotArea.FilterArea)

                        aField.Visible = False
                        aField.Caption = _row("pCaption").ToString
                        aField.Width = Integer.Parse(_row("pWidth").ToString())
                        'Console.WriteLine(aField.ToString)

                        formatType = GetFormatType(_row)

                        aField.ValueFormat.FormatType = formatType
                        aField.ValueFormat.FormatString = _row("pNumberFormat").ToString
                        DevExGridHelper.AssignPivotSummaryItemType(aField, _row)

                        aField.CellFormat.FormatType = formatType
                        aField.CellFormat.FormatString = _row("pNumberFormat").ToString

                        aField.TotalValueFormat.FormatType = formatType
                        aField.TotalValueFormat.FormatString = _row("pNumberFormat").ToString

                        aField.TotalCellFormat.FormatType = formatType
                        aField.TotalCellFormat.FormatString = _row("pNumberFormat").ToString
                    End If
                Next
            End If
            'this is where all fields are retrived and the false on visible property of the field will set all fields in the customisation form
            PivotGridControl1.OptionsCustomization.CustomizationFormStyle = DevExpress.XtraPivotGrid.Customization.CustomizationFormStyle.Excel2007
        End Using
        '
        PivotGridControl1.EndUpdate()
    End Sub

    Private Sub trapmenu(ByVal sender As System.Object, ByVal e As DevExpress.XtraPivotGrid.PivotGridMenuItemClickEventArgs) Handles PivotGridControl1.MenuItemClick
        If e.Item.Caption = "Reload Data" Then
            RefreshPivot()
        End If
    End Sub

    Private Sub RefreshPivot()
        Dim stream As System.IO.Stream = New System.IO.MemoryStream()

        ' Me.PivotGridControl1.BeginUpdate()
        PivotGridControl1.SaveLayoutToStream(stream)
        stream.Seek(0, System.IO.SeekOrigin.Begin)
        LoadPivotTableBySQLStatement(_aQueryName)
        PivotGridControl1.RestoreLayoutFromStream(stream)
        stream.Seek(0, System.IO.SeekOrigin.Begin)
        'Me.PivotGridControl1.EndUpdate()
        'Me.PivotGridControl1.Refresh
    End Sub
    ''' <summary>
    ''' We will add item menu to the contect meu of the Pivot control
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub pivotGridControl1_ShowMenu(ByVal sender As Object, ByVal e As DevExpress.XtraPivotGrid.PopupMenuShowingEventArgs) Handles PivotGridControl1.PopupMenuShowing
        Dim item As DXMenuItem
        If e.MenuType = DevExpress.XtraPivotGrid.PivotGridMenuType.HeaderArea Then
            item = New DXMenuItem("Print", New EventHandler(AddressOf printAction)) With {.Tag = sender}
            e.Menu.Items.Add(item)
            item = New DXMenuItem("Export To excel", New EventHandler(AddressOf ExportXLSX)) With {.Tag = sender}
            e.Menu.Items.Add(item)

        End If
    End Sub

    Private Sub ExportXLSX(ByVal sender As Object, ByVal e As EventArgs)
        Dim aFile As New System.Windows.Forms.SaveFileDialog
        aFile.FileName = "*.xlsx"
        If aFile.ShowDialog = DialogResult.OK Then
            PivotGridControl1.ExportToXlsx(aFile.FileName)
        End If
    End Sub

    Private Sub printAction(ByVal sender As Object, ByVal e As EventArgs)
        Dim pivot As PivotGridControl = TryCast(DirectCast(sender, DXMenuItem).Tag, PivotGridControl)
        If pivot Is Nothing Then
            Return
        End If
        PivotGridControl1.ShowPrintPreview()
    End Sub

    Private Sub BarButtonItem3_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs)
        Me.Cursor = Cursors.WaitCursor
        PivotGridControl1.FieldsCustomization()
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub FieldListBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles FieldListBarButton.ItemClick
        Me.Cursor = Cursors.WaitCursor
        PivotGridControl1.FieldsCustomization()
        Me.Cursor = Cursors.Default
    End Sub

    Private Function GetFormatType(ByVal arow As DataRow) As DevExpress.Utils.FormatType

        If (Not String.IsNullOrEmpty(arow("pNumberFormatID_FK").ToString)) Then
            Select Case CType(arow("pNumberFormatID_FK"), Guid)

                Case Base.IdRepository.META_NUMBERFORMAT_CURRENCY, Base.IdRepository.META_NUMBERFORMAT_GENERALNUMBER, Base.IdRepository.META_NUMBERFORMAT_PERCENT, Base.IdRepository.META_NUMBERFORMAT_STANDARD, Base.IdRepository.META_NUMBERFORMAT_SCIENTIFIC
                    Return DevExpress.Utils.FormatType.Numeric

                Case Base.IdRepository.META_NUMBERFORMAT_GENERALDATE, Base.IdRepository.META_NUMBERFORMAT_LONGDATE, Base.IdRepository.META_NUMBERFORMAT_LONGTIME, Base.IdRepository.META_NUMBERFORMAT_MEDIUMDATE, Base.IdRepository.META_NUMBERFORMAT_MEDIUMTIME, Base.IdRepository.META_NUMBERFORMAT_SHORTDATE, Base.IdRepository.META_NUMBERFORMAT_SHORTTIME
                    Return DevExpress.Utils.FormatType.DateTime

                Case Base.IdRepository.META_NUMBERFORMAT_FORMATEXPR
                    Return DevExpress.Utils.FormatType.Custom

                Case Else
                    Return DevExpress.Utils.FormatType.None
            End Select
        Else
            Return DevExpress.Utils.FormatType.None
        End If

    End Function

    Private Sub RefreshBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles RefreshBarButton.ItemClick
        RefreshPivot()
    End Sub

    Private Sub ExcelxlsxBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles ExcelxlsxBarButton.ItemClick
        Dim aFile As New System.Windows.Forms.SaveFileDialog() With {.FileName = "*.xlsx"}
        If aFile.ShowDialog = DialogResult.OK Then
            PivotGridControl1.ExportToXlsx(aFile.FileName)
        End If
    End Sub

    Private Sub PDFBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles PDFBarButton.ItemClick
        Dim aFile As New System.Windows.Forms.SaveFileDialog() With {.FileName = "*.pdf"}
        If aFile.ShowDialog = DialogResult.OK Then
            PivotGridControl1.ExportToPdf(aFile.FileName)
        End If
    End Sub

    Private Sub PrintBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles PrintBarButton.ItemClick
        PivotGridControl1.ShowPrintPreview()
    End Sub


    Private Sub NearTotBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles NearTotBarButton.ItemClick
        'OUTSTANDINGISSUE: Valider qu'il est nécessaire de setter ces 3 property pour montrer ou cacher les colonnes de totaux. clieng 2013-10-29.PivotGridControl1.OptionsView.ShowRowTotals = True
        PivotGridControl1.OptionsView.ShowAllTotals()
        Me.PivotGridControl1.OptionsView.RowTotalsLocation = DevExpress.XtraPivotGrid.PivotRowTotalsLocation.Near
    End Sub

    Private Sub FarTotBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles FarTotBarButton.ItemClick
        'OUTSTANDINGISSUE: Valider qu'il est nécessaire de setter ces 3 property pour montrer ou cacher les colonnes de totaux. clieng 2013-10-29.
        PivotGridControl1.OptionsView.ShowRowTotals = True
        PivotGridControl1.OptionsView.ShowAllTotals()
        Me.PivotGridControl1.OptionsView.RowTotalsLocation = DevExpress.XtraPivotGrid.PivotRowTotalsLocation.Far
    End Sub

    Private Sub TreeTotBarButton_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles TreeTotBarButton.ItemClick
        'OUTSTANDINGISSUE: Valider qu'il est nécessaire de setter ces 3 property pour montrer ou cacher les colonnes de totaux. clieng 2013-10-29.
        PivotGridControl1.OptionsView.ShowRowTotals = True
        PivotGridControl1.OptionsView.ShowAllTotals()
        Me.PivotGridControl1.OptionsView.RowTotalsLocation = DevExpress.XtraPivotGrid.PivotRowTotalsLocation.Tree
    End Sub

    Private Sub NoneTotBarButton_ItemClick(sender As Object, e As ItemClickEventArgs) Handles NoneTotBarButton.ItemClick
        'OUTSTANDINGISSUE: Valider qu'il est nécessaire de setter ces 3 property pour montrer ou cacher les colonnes de totaux. clieng 2013-10-29.
        Me.PivotGridControl1.OptionsView.RowTotalsLocation = Nothing
        PivotGridControl1.OptionsView.ShowRowTotals = False
        PivotGridControl1.OptionsView.HideAllTotals()
    End Sub
End Class
